package com.lancoo.ccas52.util;

import com.lancoo.ccas52.pojo.vo.ClassDto;
import com.lancoo.ccas52.pojo.vo.CollegeDto;
import com.lancoo.ccas52.pojo.vo.GradeDto;
import com.lancoo.ccas52.pojo.vo.MajorDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName JdbcUtil
 * @Description 简化重复sql
 * @Author quin
 * @Date 2023/1/6 10:55
 */
@Component
@Slf4j
public class JdbcUtil {

    @Resource
    JdbcTemplate jdbcTemplate;

    /**
     * 获取学院/开课单位
     * @param tableName
     * @param taskId
     * @return
     */
    public List<CollegeDto> queryCollege(String tableName,Integer taskId){
        String sql = "SELECT DISTINCT college_id, college_name from " + tableName +" where task_id = " + taskId;
        List<CollegeDto> collegeDtos = new ArrayList<>();
        try {
            collegeDtos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CollegeDto.class));
        } catch (Exception e) {
            log.info("sql语句：{}",sql);
            e.printStackTrace();
        }
        return collegeDtos;
    }

    /**
     * 获取年级
     * @param tableName
     * @param taskId
     * @return
     */
    public List<GradeDto> queryGrade(String tableName,Integer taskId){
        String sql = "SELECT DISTINCT grade_id, grade_name from " + tableName +" where task_id = " + taskId;
        List<GradeDto> gradeDtos = new ArrayList<>();
        try {
            gradeDtos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GradeDto.class));
        } catch (Exception e) {
            log.info("sql语句：{}",sql);
            e.printStackTrace();
        }
        return gradeDtos;
    }

    /**
     * 获取专业
     * @param tableName
     * @param taskId
     * @return
     */
    public List<MajorDto> queryMajor(String tableName, Integer taskId){
        String sql = "SELECT DISTINCT major_id, major_name from " + tableName +" where task_id = " + taskId;
        if(tableName.equals("major")){
            sql = "SELECT DISTINCT base_major_id as major_id , major_name from " + tableName +" where task_id = " + taskId;
        }
        List<MajorDto> majorDtos = new ArrayList<>();
        try {
            majorDtos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(MajorDto.class));
        } catch (DataAccessException e) {
            log.info("sql语句：{}",sql);
            e.printStackTrace();
        }
        return majorDtos;
    }
    /**
     * 获取班级
     * @param tableName
     * @param taskId
     * @return
     */
    public List<ClassDto> queryClass(String tableName, Integer taskId){
        String sql = "SELECT DISTINCT class_id, class_name from " + tableName +" where task_id = " + taskId;
        List<ClassDto> classDtos = new ArrayList<>();
        try {
            classDtos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ClassDto.class));
        } catch (DataAccessException e) {
            log.info("sql语句：{}",sql);
            e.printStackTrace();
        }
        return classDtos;
    }

    public void deleteRoomProhibit(String tableName, List<Integer> roomId, Integer taskId){
        StringBuilder roomIdString = new StringBuilder("(");
        for (Integer id : roomId) {
            roomIdString.append(id.toString()).append(",");
        }
        String s = roomIdString.deleteCharAt(roomIdString.lastIndexOf(",")).toString();
        String sql = "DELETE FROM " + tableName + " WHERE task_id = " + taskId + " and room_id in" + s +")";
        try {
            jdbcTemplate.execute(sql);
        } catch (DataAccessException e) {
            log.error("sql语句：{}",sql);
            e.printStackTrace();
        }
    }

    public void deleteCourseProhibit(String tableName, List<Integer> teachingClassId, Integer taskId){
        StringBuilder roomIdString = new StringBuilder("(");
        for (Integer id : teachingClassId) {
            roomIdString.append(id.toString()).append(",");
        }
        String s = roomIdString.deleteCharAt(roomIdString.lastIndexOf(",")).toString();
        String sql = "DELETE FROM " + tableName + " WHERE teaching_class_id in" + s +")";
        try {
            jdbcTemplate.execute(sql);
        } catch (DataAccessException e) {
            log.error("sql语句：{}",sql);
            e.printStackTrace();
        }
    }
}
